set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_dm.dm_fastest_route_area_effective
select
     update_date
    ,is_main_route
    ,in_from_regional_code
    ,in_from_regional_desc
    ,in_from_financial_center_code
    ,in_from_financial_center_desc
    ,in_from_provider_code
    ,in_from_provider_desc
    ,in_from_city_code
    ,in_from_city_desc
    ,in_from_area_code
    ,in_from_area_desc
    ,in_collect_code
    ,in_collect_name
    ,in_from_code
    ,in_from_name
    ,in_to_code
    ,in_to_name
    ,end_center_code
    ,end_center
    ,start_center_code
    ,start_center
    ,out_to_code
    ,out_to_name
    ,out_to_regional_code
    ,out_to_regional_desc
    ,out_to_financial_center_code
    ,out_to_financial_center_desc
    ,out_to_provider_code
    ,out_to_provider_desc
    ,out_from_code
    ,out_from_name
    ,out_to_city_code
    ,out_to_city_desc
    ,out_to_area_code
    ,out_to_area_desc
    ,out_collect_code
    ,out_collect_name
    ,total_days_use
    ,total_time_use
    ,total_days_t
    ,whole_route
    ,all_line_name
    ,is_main_city
    ,platform_code_cn
    ,platform_cn
    ,effective_times_cn
    ,platform_code_zt
    ,platform_zt
    ,effective_times_zt
    ,platform_code_sf
    ,platform_sf
    ,effective_times_sf
    ,platform_code_yt
    ,platform_yt
    ,effective_times_yt
    ,platform_code_yd
    ,platform_yd
    ,effective_times_yd
    ,platform_code_thd
    ,platform_thd
    ,effective_times_thd
    ,platform_code_zjs
    ,platform_zjs
    ,effective_times_zjs
    ,dt
from (
    select
        *,row_number() over(partition by in_from_area_code,out_to_area_code order by search_type asc,total_time_use asc,in_edge_planned_departure asc,in_edge2_planned_departure asc,
                                            extra_in_planned_departure asc,e1_planned_departure asc,e2_planned_departure asc,e3_planned_departure asc,e4_planned_departure asc,
                                            e5_planned_departure asc,e6_planned_departure asc,out_edge_planned_departure asc,extra_out_planned_departure asc,out_edge2_planned_departure asc) as rn
    from jms_dm.dm_route_city_fastest_dt --路由区县时效最快表
    where dt = '{{ execution_date | cst_ds }}'
    and is_area_fastest = 1
) a where a.rn = 1
distribute by dt,pmod(hash(rand()),15);


